package teamCservlets;
import java.io.Serializable;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.io.IOException;
import java.util.ArrayList;
import java.util.logging.FileHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Types;

public class DatabaseInterfacePerson extends DatabaseInterface {

	// Key fields:


	public DatabaseInterfacePerson() {
		super();
	}  // End constructor

	// The getPerson method loads a person by e-mail address, not by
	// Person_ID.  Returns a PersonBean if the e-mail address is in the
	// database or "null" if the e-mail address is NOT in the database.
	public synchronized PersonBean getPerson(String emailAddress) {

		try {

			// Execute query.

			String personQuery = new String("SELECT * FROM PERSON WHERE Email_Address = ?");

			LogInfo("Person Query: " + personQuery);

			// Create prepared statement object.
			preparedStatement = connection.prepareStatement(personQuery);
			// Set the e-mail address parameter.
			preparedStatement.setString(1,emailAddress);

			runSQLstatement();
			// If result is null, return null.  Otherwise, generate a PersonBean
			// object and return it.

			if(resultSet == null) {
				LogWarning("DatabaseInterfacePerson: Result set is null");
				return null;
			} else {

				if(!resultSet.isBeforeFirst()) {
					LogWarning("DatabaseInterfacePerson: The resultSet object is empty!");
					return null;
				} else {
					// Create new PersonBean object
					resultSet.first();
					PersonBean person = new PersonBean();

					// Get the query results.
					person.setFirstName(resultSet.getString("First_Name"));
					person.setLastName(resultSet.getString("Last_Name"));
					person.setMiddleName(resultSet.getString("Middle_Name"));
					person.setEmailAddr(resultSet.getString("Email_Address"));
					person.setId(resultSet.getInt("Person_ID"));

					// Close the statement and the connection.

					cleanup();

					return person;
				}
			} // End code that returns PersonBean

		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		}  catch (NullPointerException npe) {

			LogException(npe);
			return null;
		}  catch (Exception e) {

			LogException(e);
			return null;
		}

	} // End getPerson method


	// The getPerson method loads a person by e-mail address, not by
	// Person_ID.  Returns a PersonBean if the e-mail address is in the
	// database or "null" if the e-mail address is NOT in the database.
	public synchronized PersonBean getPersonByID(int personID) {

		try {

			String personQuery = new String("SELECT * FROM PERSON WHERE Person_ID = ?");

			LogInfo(personQuery);

			preparedStatement = connection.prepareStatement(personQuery);
			preparedStatement.setInt(1,personID);
			runSQLstatement();

			// If result is null, return null.  Otherwise, generate a PersonBean
			// object and return it.

			if(resultSet == null) {
				LogWarning("DatabaseInterfacePerson: Result set is null");
				return null;
			} else {

				if(!resultSet.isBeforeFirst()) {
					LogWarning("DatabaseInterfacePerson: Result set is empty.");
					return null;
				}

				// Create new PersonBean object

				PersonBean person = new PersonBean();
				resultSet.first();
				// Get the query results.
				person.setFirstName(resultSet.getString("First_Name"));
				person.setLastName(resultSet.getString("Last_Name"));
				person.setMiddleName(resultSet.getString("Middle_Name"));
				person.setEmailAddr(resultSet.getString("Email_Address"));
				person.setId(resultSet.getInt("Person_ID"));

				cleanup();

				return person;
			} // End code that returns PersonBean

		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		}  catch (NullPointerException npe) {

			LogException(npe);
			return null;
		}  catch (Exception e) {

			LogException(e);
			return null;
		}

	} // End getPersonByID method

	// The getPersonList method retrieves all entries from the Person table,
	// and provides an ArrayList of PersonBean.
	public synchronized ArrayList<PersonBean> getPersonList() {

		try {

			ArrayList<PersonBean> personList = new ArrayList<PersonBean>();

			String personQuery = new String("SELECT Person_ID, Last_Name, First_Name, Middle_Name, Email_Address FROM PERSON");

			preparedStatement = connection.prepareStatement(personQuery);

			runSQLstatement();

			// If result is null, return null.  Otherwise, create a list of all persons.
			if(resultSet == null) {
				LogWarning("DatabaseInterfacePerson: Array Result set is null");
				return null;
			} else {
				// For each row returned...
				while (resultSet.next()) {

					// Create a new PersonBean and store the person's info in it
					PersonBean person = new PersonBean();
					person.setFirstName(resultSet.getString("First_Name"));
					person.setLastName(resultSet.getString("Last_Name"));
					person.setMiddleName(resultSet.getString("Middle_Name"));
					person.setEmailAddr(resultSet.getString("Email_Address"));
					person.setId(resultSet.getInt("Person_ID"));

					// Add the PersonBean to the ArrayList
					personList.add(person);
				}

				// Close the statement and connection.

				cleanup();

				return personList;
			} // End code that builds the person list

		} catch (SQLException sqle) {

			LogException(sqle);
			return null;
		}  catch (NullPointerException npe) {

			LogException(npe);
			return null;
		}  catch (Exception e) {

			LogException(e);
			return null;
		}

	} // End getPersonList method


} // End DatabaseInterfacePerson